/*
 *文件名： DbUtil.java
 *版权： Copyright by 云天励飞 intellif.com
 *描述： 数据库操作工具类
 *创建人：mozping
 *创建时间： 2018/9/7 9:55
 *修改理由：
 *修改内容：
 */
package indi.mozping.util;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.io.*;
import java.nio.file.Paths;
import java.sql.*;
import java.util.ArrayList;
import java.util.Properties;

/**
 * MySql数据库操作工具类
 * 主要用于初始化Flyway脚本
 *
 * @author mozping
 * @version 1.0
 * @date 2018/9/7 9:55
 * @see DbUtil
 * @since JDK1.8
 */
public class DbUtil {

    private static Logger LOG = LoggerFactory.getLogger(DbUtil.class);
    private static final String NOT_DEFINED = "notDefined";
    private static final String JDBC_DRIVER = "com.mysql.jdbc.Driver";
    private static final String SQL_QUERY_DB = "SHOW DATABASES";
    private static final String KEY_FLYWAY_URL = "flyway.url";
    private static final String KEY_FLYWAY_USER = "flyway.user";
    private static final String KEY_FLYWAY_PSW = "flyway.password";
    private static final String CLASS_PATH = "classpath:";


    /**
     * @param filePath flyway配置文件路径
     * @Description: 初始化flyway
     * @date 2019/7/18 9:47
     * 参数示例如下：
     * 示例1: ./flyway.conf 或者 flyway.conf ，表示flyway.conf配置文件在ide中和pom.xml同级目录，或者和可执行jar包在同级目录
     * 示例2: ./config/flyway.conf 或者 config/flyway.conf，表示flyway.conf配置文件在ide中和pom.xml同级目录的config下面，
     * 或者在可执行jar包同级目录的config下面
     * 示例3: classpath:flyway.conf 或者 classpath:db/flyway.conf，表示flyway.conf配置文件在ide中resource下面，或
     * 者resource下面的对应db目录下
     * 示例4: 也可以传递其他合法路径，比如: E:/flyway5.conf
     */
    public static Properties initConfig(String filePath) throws Exception {
        if (filePath == null || filePath.length() == 0) {
            throw new IllegalArgumentException("filePath can not be null or empty string!!!");
        }
        return loadPropertyFromFile(filePath);
    }


    /**
     * 检查Flyway需要初始化的数据库是否存在：
     * 1.若存在，则不做任何事情
     * 2.若不存在，则创建数据库
     *
     * @param property 初始化参数配置对象
     * @throws IOException 加载配置异常
     */
    private static void checkDbAutoCreatedIfNotExists(Properties property) throws IOException {

        Connection conn = null;
        Statement stmt = null;

        //1.获取原始配置参数
        String flywayUrl = property.getProperty(KEY_FLYWAY_URL, NOT_DEFINED);
        String username = property.getProperty(KEY_FLYWAY_USER, NOT_DEFINED);
        String password = property.getProperty(KEY_FLYWAY_PSW, NOT_DEFINED);
        if (NOT_DEFINED.equals(flywayUrl) || NOT_DEFINED.equals(username) || NOT_DEFINED.equals(password)) {
            LOG.warn("database info of dbUrl or username or password is wrong!");
        }
        //提取参数
        String dbUrl = flywayUrl.substring(0, flywayUrl.indexOf('/', 15)) + "?connectTimeout=5000&socketTimeout=5000";
        int endOfDbName = flywayUrl.contains("?") ? flywayUrl.indexOf('?') : 100;
        String dbName = flywayUrl.substring(flywayUrl.indexOf('/', 15) + 1, endOfDbName);

        //2.连接数据库并检查
        try {
            //STEP 1: Register JDBC driver
            Class.forName(JDBC_DRIVER);

            //STEP 2: Open a connection
            LOG.info("Connecting to database with url :" + dbUrl);
            DriverManager.setLoginTimeout(5);
            LOG.info("Before Connecting... ");
            conn = DriverManager.getConnection(dbUrl, username, password);
            LOG.info("End Of Connecting... ");
            //STEP 3: Execute a query
            LOG.info("Connect success and ready to check database...");
            stmt = conn.createStatement();
            ResultSet rs = stmt.executeQuery(SQL_QUERY_DB);
            while (rs.next()) {
                // 如果数据库已经存在则直接返回
                String dbNameOfQueryResult = rs.getString("Database");
                LOG.debug("Found database: " + dbNameOfQueryResult);
                if (dbNameOfQueryResult != null && dbNameOfQueryResult.equalsIgnoreCase(dbName)) {
                    LOG.info("Database " + dbName + " is already exists in the DB : " + dbUrl + " , need not to created!");
                    return;
                }
            }
            //如果数据库不存在则创建数据库
            LOG.info("Ready to create database : " + dbName);
            String sqlCreateDb = "CREATE DATABASE IF NOT EXISTS " + dbName + " DEFAULT CHARSET utf8 COLLATE utf8_general_ci; ";
            stmt.executeUpdate(sqlCreateDb);
            LOG.info("Database created successfully...");
        } catch (SQLException se) {
            //Handle errors for JDBC
            se.printStackTrace();
        } catch (Exception e) {
            //Handle errors for Class.forName
            e.printStackTrace();
        } finally {
            //finally block used to close resources
            try {
                if (stmt != null) {
                    stmt.close();
                }
            } catch (SQLException se2) {
                se2.printStackTrace();
            }// nothing we can do
            try {
                if (conn != null) {
                    conn.close();
                }
            } catch (SQLException se) {
                se.printStackTrace();
            }//end finally try
        }//end try
        LOG.info("Check database finished...");
    }


    public static Properties loadPropertyFromFile(String filePath) throws Exception {
        Properties property = new Properties();
        InputStream fi;
        try {
            fi = getFlywayConfigFileAsStream(filePath);
            property.load(fi);
        } catch (FileNotFoundException e) {
            LOG.warn("flyway.conf load failed!");
            throw e;
        } catch (Exception e) {
            LOG.warn("flyway init failed!");
            throw e;
        }
        return property;
    }

    /**
     * 加载flyway的配置文件，并返回Properties的配置对象
     *
     * @return java.util.Properties 配置对象
     * @throws IOException
     */
    private static Properties loadPropertyFromStream() throws Exception {

        Properties property = new Properties();
        InputStream fi;

        try {
            fi = getFlywayConfigFileAsStream();
            property.load(fi);
        } catch (FileNotFoundException e) {
            LOG.warn("flyway.conf load failed!");
            throw e;
        } catch (Exception e) {
            LOG.warn("flyway init failed!");
            throw e;
        }
        return property;
    }

    private static InputStream getFlywayConfigFileAsStream(String filePath) throws FileNotFoundException {
        //1.处理classpath下面的路径情况
        if (filePath != null && filePath.startsWith(CLASS_PATH)) {
            int index = filePath.indexOf(":") + 1;
            String fp = filePath.substring(index);
            InputStream in = DbUtil.class.getClassLoader().getResourceAsStream(fp);
            if (in != null) {
                LOG.info("load flyway.conf from " + filePath + " success!");
                return in;
            } else {
                throw new FileNotFoundException();
            }
        }
        //2.处理其他路径的情况
        File configFile = new File(filePath);
        if (configFile.exists() && configFile.isFile() && configFile.canRead()) {
            LOG.info("load flyway.conf ,the file location is :" + configFile.getPath());
            try {
                return new FileInputStream(configFile);
            } catch (Exception e) {
                LOG.warn("load flyway.conf in :" + configFile.getPath() + " failed!");
                throw new FileNotFoundException();
            }
        }
        LOG.info("load flyway.conf from " + filePath + " failed !!");
        throw new FileNotFoundException(filePath + " is invalid!!");
    }

    /**
     * 读取Flyway配置文件
     * 1.持续集成部署环境中，读取api.jar当前平级目录下的flyway.conf配置文件
     * 2.未采用持续集成部署，读取api.jar当前平级目录下config目录下的flyway.conf配置文件
     * 3.开发环境IDE中，读取classpath下的flyway.conf配置文件
     * (pom文件的build配置会将flyway.conf移动到config下)
     * 按照123的顺序依次加载，加载到即不会再继续往后面加载
     *
     * @return java.io.InputStream
     */
    private static InputStream getFlywayConfigFileAsStream() throws FileNotFoundException {

        ArrayList<File> candidateFileArr = new ArrayList<>();

        //1.在部署环境的当前目录下找
        File fileDeploy = Paths.get("./", "flyway.conf").toFile();

        //2.在当前的config路径下找
        File fileConfig = Paths.get("./config/", "flyway.conf").toFile();
        candidateFileArr.add(fileDeploy);
        candidateFileArr.add(fileConfig);

        for (File file : candidateFileArr) {
            if (file.exists() && file.isFile() && file.canRead()) {
                LOG.warn("load flyway.conf ,the file location is :" + file.getPath());
                try {
                    return new FileInputStream(file);
                } catch (Exception e) {
                    LOG.warn("load flyway.conf in :" + file.getPath() + " failed!");
                }
            }
        }

        //3.在开发环境的classpath下面找(resources下)
        try {
            return getFileFromClassPath();
        } catch (Exception e) {
            LOG.warn("classpath found flyway.conf failed!");
        }
        LOG.warn("flyway config file no found! please check ...");
        throw new FileNotFoundException();
    }


    /**
     * 从classpath下寻找配置文件
     *
     * @return java.io.InputStream 文件输入流对象
     * @throws FileNotFoundException
     */
    private static InputStream getFileFromClassPath() throws FileNotFoundException {

        InputStream in = DbUtil.class.getClassLoader().getResourceAsStream("db/flyway.conf");
        if (in != null) {
            LOG.info("load flyway.conf from classpath success!");
            return in;
        } else {
            throw new FileNotFoundException();
        }
    }

}
